package com.xenstra.bhavcopydownloader;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Date;
import java.util.List;

/**
 * Uploads the CSV files into the database table by performing ETL steps.
 * 
 * ETL Steps :
 * 
 * 1. Upload the CSV files into the staging tables.
 * 2. Clean up unncessary records from the staging tables.
 * 3. Transform and move cleaned data into the main tables.
 * 
 * @author sandip.bhattacharya
 *
 */
public class BhavCopyDBUploader {

	private Connection connection;
	
	public void init()throws Exception{
		connectToDB();
	}
	
	public void uploadToDB(File file, boolean isZipped, boolean deleteFileAfterUpload)throws Exception{
		uploadToDB(file.getAbsolutePath(), isZipped, deleteFileAfterUpload);
	}
	
	public void uploadToDB(String fileName, boolean isZipped, boolean deleteFileAfterUpload){
		File csvFile = null;
		try {
			if(isZipped){
				// Unzip to get the raw file.
				csvFile = Utils.unzipFileInTempFolder(fileName);
			}else{
				csvFile = new File(fileName);
			}
			
			uploadCSV(csvFile);
			System.out.print(".");
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("Error :" + e);
			System.out.println(fileName);
		}finally{
			if((deleteFileAfterUpload || isZipped) && csvFile != null){
				csvFile.delete();
			}
		}
	}
	
	private void connectToDB()throws Exception{
		if(connection == null || connection.isClosed()){
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","bills","AgreeYa!@#$");
		}
	}
	
	private void uploadCSV(File file) throws Exception{
		if(file == null)return;
		
		String formattedFilePath = file.getAbsolutePath().replace('\\', '/');

		String sqlUploadFileToStageTable = null;
		String sqlCallStoredProcedure = null;
		
		if(file.getName().startsWith("fo")){
			//*
			// F & O raw file.
			sqlUploadFileToStageTable = "LOAD DATA INFILE '" + formattedFilePath + "' IGNORE INTO TABLE stage_fo "
					+ "FIELDS TERMINATED BY ','  "
					+ "IGNORE 1 LINES "
					+ "(INSTRUMENT,SYMBOL,@EXPIRY_DT,STRIKE_PR,OPTION_TYP,OPEN,HIGH,LOW,CLOSE,SETTLE_PR,CONTRACTS,VAL_INLAKH,OPEN_INT,CHG_IN_OI,@TIMESTAMP)  "
					+ "SET EXPIRY_DT = STR_TO_DATE(@EXPIRY_DT, '%d-%M-%Y'), "
					+ "TIMESTAMP = STR_TO_DATE(@TIMESTAMP, '%d-%M-%Y')";
			
			sqlCallStoredProcedure = "{call moveFODataToMainTables()}";
			//*/
		}else if(file.getName().startsWith("cm")){
			// Equities raw file.
			sqlUploadFileToStageTable = "LOAD DATA INFILE '" + formattedFilePath + "' IGNORE INTO TABLE stage_cm "
					+ "FIELDS TERMINATED BY ',' "
					+ "IGNORE 1 LINES "  
					+ "(SYMBOL,SERIES, OPEN, HIGH, LOW, CLOSE, LAST, PREVCLOSE, TOTTRDQTY, TOTTRDVAL, @TIMESTAMP, TOTALTRADES, @dummy) "  
					+ "SET TIMESTAMP = STR_TO_DATE(@TIMESTAMP, '%d-%M-%Y')";
			
			sqlCallStoredProcedure = "{call moveEQDataToMainTables()}";
		}
		
		if(sqlCallStoredProcedure != null){
			Statement stmt = connection.createStatement();
			stmt.executeUpdate(sqlUploadFileToStageTable);
			stmt.executeUpdate(sqlCallStoredProcedure);
			stmt.close();			
		}
	}
	
	public static void main(String[] args) throws Exception{
		/*System.out.println(System.getProperty("java.io.tmpdir"));
		BhavCopyDBUploader uploader = new BhavCopyDBUploader();
		uploader.uploadToDB("E:\\anubhav\\bhavcopies\\bhavcopies\\2013\\09\\fo06SEP2013bhav.csv.zip", true, true);*/
		
		BhavCopyDBUploader uploader = new BhavCopyDBUploader();
		uploader.init();
		
		String[] folders = {
				"E:\\bhavcopies\\2015"

				/*"E:\\bhavcopies\\2010\\01",
				"E:\\bhavcopies\\2010\\02",
				"E:\\bhavcopies\\2010\\03",
				"E:\\bhavcopies\\2010\\04",
				"E:\\bhavcopies\\2010\\05",
				"E:\\bhavcopies\\2010\\06",
				"E:\\bhavcopies\\2010\\07",
				"E:\\bhavcopies\\2010\\08",
				"E:\\bhavcopies\\2010\\09",
				"E:\\bhavcopies\\2010\\10",
				"E:\\bhavcopies\\2010\\11",
				"E:\\bhavcopies\\2010\\12"*/
			};

		Date startDate = new Date(115,1,3), endDate = new Date(115,2,7);
		String fileExtWildCard = "fo.*";

		for(String folder : folders){
			//List<File> files = Utils.getAllFilesInFolder(folder, fileExtWildCard);
			List<File> files = Utils.getAllFilesInFolder(folder, startDate, endDate);
			System.out.println("\n" + files.size() + " files in " + folder);
			
			for(File file : files){
				uploader.uploadToDB(file, true, true);
			}			
		}
	}
}
